import pandas as pd
from datetime import datetime, timedelta, date
#from plyer import notification
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
import glob

DEBUG = False # 开发时使用 True

class BirthdayNotice:
    def __init__(self, source_xlsx):
        self.today = datetime.now().strftime('%Y-%m-%d') # 2023-06-24

        today = date.today()
        #print(today)
        #next_month = today.replace(month=today.month + 1) # 2.获取下一个月
        next_month = today + timedelta(days=30)
        #print(next_month)
        self.next_month_date = next_month.strftime("%Y-%m-%d")
        #print(last_month_date)

        self.get_users(self.today[:4] + source_xlsx)
        #print(self.users)

    def get_users(self, source_xlsx): 
        self.users = pd.read_excel(source_xlsx, sheet_name='生产部', header=1)
        self.users = pd.concat([pd.read_excel(source_xlsx, sheet_name='非生产部', header=1), self.users])
        self.users[['出生年月']] = self.users[['出生年月']].astype(str)
        self.users = self.users.rename(columns={
            '出生年月':'生日',
            '所在部门':'组别',
        })
        self.users['日期'] = self.users['生日'].str.slice(5, 10)
        self.users = self.users[['姓名','生日','日期','组别','工龄']]
        self.users = self.users[self.users['姓名'].notna()]
        self.users = self.users.sort_values(by='日期')

    def notice_all(self):
        """
            每个月28提醒下个月所有需要贺卡的人员清单
            birthday[5:7] == month
        """
        # 是否是28
        day = self.today[-2:] 
        #if day == ('26' if DEBUG else '01'):
        if day == ('26' if DEBUG else '28'):
            this_month = self.next_month_date[5:7]
            #print(this_month)
            # 筛选出本月的所有记录
            this_month_users = self.users[self.users['生日'].str.slice(5, 7) == this_month]
            #print(this_month_users)
            if this_month_users.empty:
                return
            self.to_excel(this_month_users)

            message = this_month_users.head(5).to_string(index=False) + '\r\n' + \
                f'等{len(this_month_users)}位' if len(this_month_users) > 5 else \
                this_month_users.head(5).to_string(index=False) 

            data = {
                'title': '下月生日人员清单',
                'message': message 
            }
            self.notify2(data)
        
    
    def notice_one(self):
        """
            每天提醒当天有哪些人员需要发贺卡
            birthday == today
        """
        m_d = self.today[5:10] 
        today_users = self.users[self.users['生日'].str.slice(5, 10) == m_d]
        #print(today_users)
        if today_users.empty:
            return
        message = today_users.head(5).to_string(index=False) + '\r\n' + \
            f'等{len(today_users)}位' if len(today_users) > 5 else \
            today_users.head(5).to_string(index=False) 
        data = {
            'title': '今天生日人员清单',
            'message': message

        }
        self.notify2(data)

    def notify(self, data):
        """
            消息提醒
        """
        notification.notify(
            title = data['title'],
            message = data['message'],
            app_name = '生日提醒',
            app_icon = 'favicon.ico',
            ticker = '生日提醒',
            timeout = 30 if DEBUG else 43200,
        )

    def notify2(self, data):
        from tkinter import Tk
        from tkinter import messagebox
        #小弹窗
        window = Tk()
        window.wm_attributes('-topmost',1)
        window.withdraw()
        messagebox.showinfo(data['title'], data['message'])
        
        # 发现窗口没法居中
        #import ttkbootstrap as ttk
        #from ttkbootstrap.dialogs import Messagebox
        ##root = ttk.Window()
        #Messagebox.ok(
        #    message=data['message'],
        #    title=data['title'],
        #    alert=True
        #)
         
    
    def reset_col(self, filename):
    	wb=load_workbook(filename)
    	for sheet in wb.sheetnames:
    		ws=wb[sheet]
    		df=pd.read_excel(filename,sheet).fillna('-')
    		df.loc[len(df)]=list(df.columns)						#把标题行附加到最后一行
    		for col in df.columns:				
    			index=list(df.columns).index(col)					#列序号
    			letter=get_column_letter(index+1)					#列字母
    			collen=df[col].apply(lambda x:len(str(x).encode())).max()	#获取这一列长度的最大值 当然也可以用min获取最小值 mean获取平均值
    			ws.column_dimensions[letter].width=collen*1.2+4		#也就是列宽为最大长度*1.2 可以自己调整
    
    	wb.save(filename)

    def to_excel(self, df):
        """
            人员清单生成excel导出
        """
        # 同名问题
        filename = '生日人员清单' + self.next_month_date[:7]
        ext = '.xlsx'
        files = glob.glob("*") # 获取当前路径下所有文件
        #print(files)
        #print(filename)
        files_target = [filename + ext]
        for file in files:
            if filename in file:
                files_target.append(file)
        sorted(files_target)
        #print(files_target)
        filename = files_target[-1].split('.')[0]
        if filename + ext in files:
            if '_' not in filename:
                filename = filename + '_1'
            else:
                #print('111')
                filename = filename.split('_')[0] + '_' + str(int(filename.split('_')[1]) + 1)
        #print(filename)
        df.to_excel(filename + ext, index=False)
        self.reset_col(filename + ext)

if __name__ == '__main__':
    bn = BirthdayNotice('花名册.xlsx')
    bn.notice_all()
    bn.notice_one()
    #bn.notify()
